#!/usr/bin/python
# -*- coding:utf-8 -*-
# @author  : micah
# @time    : 2023/9/2 23:50
# @function: the script is used to do something.
# @version :
import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='123456', db='school')
cursor = conn.cursor()

# 1. 建立学生表
drop_table_query = "DROP TABLE IF EXISTS students"  # 删除旧的学生表（如果存在）
cursor.execute(drop_table_query)
create_table_query = '''
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    age INT,
    sex VARCHAR(10),
    hobbies VARCHAR(255)
)
'''
try:
    cursor.execute(create_table_query)
    print("CREATE TABLE SUCCESS.")
except Exception as ex:
    print(f"CREATE TABLE FAILED,CASE:{ex}")
# finally:
#     # 关闭数据库连接
#     conn.close()

# 2. 增加四条数据
insert_data_query = '''
INSERT INTO students (name, age, sex, hobbies) VALUES
    ('张三', 20, '男', '篮球'),
    ('李四', 22, '女', '足球'),
    ('王五', 21, '男', '游泳'),
    ('赵六', 19, '女', '跑步')
'''
try:
    cursor.execute(insert_data_query)
    conn.commit()
    print('数据插入成功...')
except Exception as e:
    print(f'数据插入失败: {e}')
    # 如果发生错误就回滚
    conn.rollback()

# 3. 查询sex为男的数据
select_male_data_query = "SELECT * FROM students WHERE sex = '男'"
try:
    cursor.execute(select_male_data_query)
    male_data = cursor.fetchall()
    print("查询成功...")
    print("性别为男的学生数据：")
    for row in male_data:
        print(row)
except Exception as e:
    print(f'查询错误: {e}')

# 4. 删除id=3的数据
delete_data_query = "DELETE FROM students WHERE id = 3"
try:
    cursor.execute(delete_data_query)
    conn.commit()
    print('数据删除成功...')
except Exception as e:
    print(f'数据删除失败: {e}')
    conn.rollback()

# 5. 将sex为女的修改为男
update_data_query = "UPDATE students SET sex = '男' WHERE sex = '女'"
try:
    cursor.execute(update_data_query)
    conn.commit()
    print('数据更新成功...')
except Exception as e:
    print(f'更新失败: {e}')
    conn.rollback()

# 关闭数据库连接
cursor.close()
conn.close()
